package com.xtedu.examproject.dao.impl;

import com.xtedu.examproject.dao.base.CourseDao;
import com.xtedu.examproject.entity.Course;
import com.xtedu.examproject.entity.Teacher;
import com.xtedu.examproject.entity.TeacherCourse;
import com.xtedu.examproject.util.DBUtil;
import com.xtedu.examproject.util.PageHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CourseDaoImpl implements CourseDao {
    @Override
    public int insert(Course course) throws Exception {
        String sql = "insert into courses (course_name,subject_id,sort,create_date,description,state) values (?,?,?,?,?,?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, course.getName());
            ps.setInt(2, course.getSubjectId());
            ps.setInt(3, course.getSort());
            ps.setDate(4, course.getCreateDate());
            ps.setString(5, course.getDescription());
            ps.setBoolean(6, course.isState());
            return ps.executeUpdate();
        }
    }

    @Override
    public int update(Course course) throws Exception {
        String sql = "update courses set course_name=?,subject_id=?,sort=?,create_date=?,description=?,state=? where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, course.getName());
            ps.setInt(2, course.getSubjectId());
            ps.setInt(3, course.getSort());
            ps.setDate(4, course.getCreateDate());
            ps.setString(5, course.getDescription());
            ps.setBoolean(6, course.isState());
            return ps.executeUpdate();
        }
    }

    @Override
    public int delete(Course course) throws Exception {
        String sql = "delete from courses where id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, course.getId());
            return ps.executeUpdate();
        }
    }

    @Override
    public int deleteById(int id) throws Exception {
        return 0;
    }

    @Override
    public Course findById(int id) throws Exception {
        String sql = "select * from courses where id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            Course course = new Course();
            while (rs.next()) {
                course.setId(rs.getInt("id"));
                course.setName(rs.getString("course_name"));
                course.setSubjectId(rs.getInt("subject_id"));
                course.setDescription(rs.getString("description"));
                course.setState(rs.getBoolean("state"));
            }
            return course;
        }
    }

    @Override
    public List<Course> findAll() throws Exception {
        String sql = "select * from courses WHERE state = 1 ORDER BY sort ASC";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            List<Course> courses = new ArrayList<>();
            while (rs.next()) {
                Course course = new Course();
                course.setId(rs.getInt("id"));
                course.setName(rs.getString("course_name"));
                course.setSubjectId(rs.getInt("subject_id"));
                course.setSort(rs.getInt("sort"));
                course.setCreateDate(rs.getDate("create_date"));
                course.setDescription(rs.getString("description"));
                course.setState(rs.getBoolean("state"));
                courses.add(course);
            }
            return courses;
        }
    }


    @Override
    public PageHelper findAllByPage(int pageindex, int pagesize) throws Exception {
        return null;
    }

    @Override
    public PageHelper findPageByRoleIds(int pageindex, int pagesize, int... roleIds) throws Exception {
        return null;
    }

    @Override
    public PageHelper findAll(int pageindex, int pagesize) throws Exception {
        String sql = "SELECT * FROM courses WHERE state = 1 ORDER BY sort ASC LIMIT ?, ?";
        String countSql = "SELECT COUNT(*) FROM courses WHERE state = 1";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                PreparedStatement countPs = conn.prepareStatement(countSql)
        ) {
            // 设置分页参数
            ps.setInt(1, (pageindex - 1) * pagesize);
            ps.setInt(2, pagesize);
            // 查询当前页数据
            ResultSet rs = ps.executeQuery();
            List<Course> courses = new ArrayList<>();
            while (rs.next()) {
                Course course = new Course();
                course.setId(rs.getInt("id"));
                course.setName(rs.getString("course_name"));
                course.setSubjectId(rs.getInt("subject_id"));
                course.setSort(rs.getInt("sort"));
                course.setCreateDate(rs.getDate("create_date"));
                course.setDescription(rs.getString("description"));
                course.setState(rs.getBoolean("state"));
                courses.add(course);
            }

            // 查询总记录数
            ResultSet countRs = countPs.executeQuery();
            int totalCount = 0;
            if (countRs.next()) {
                totalCount = countRs.getInt(1);
            }

            // 计算总页数
            int totalPages = (int) Math.ceil((double) totalCount / pagesize);

            return new PageHelper<>(courses, pageindex, pagesize, totalCount, "/coursemanage/", totalPages);
        }
    }


    @Override
    public PageHelper findByRoleIds(int pageindex, int pagesize, int... roleIds) throws Exception {
        return null;
    }


    public boolean deleteCourseById(int courseId) {
        String sql = "DELETE FROM courses WHERE id = ?";

        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql)
        ) {
            ps.setInt(1, courseId);
            return ps.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }


    /**
     * 查询时返回分页
     * 根据角色id查询课程
     *
     * @param pageIndex
     * @param pageSize
     * @param roleId
     * @return null
     */
    public PageHelper<Course> findPageSelectByRoleId(int pageIndex, int pageSize, int roleId) throws Exception {
        String sql = "select u.user_name,\n" +
                "s.sub_name,\n" +
                "c.*,\n" +
                "t.teacher_name\n" +
                "from users u\n" +
                "left join teachers_subjects ts on u.info_id = ts.teacher_id\n" +
                "left join teachers_courses tc on ts.teacher_id = tc.teacher_id\n" +
                "left join subjects s on s.id = ts.subject_id\n" +
                "left join courses c on c.id = tc.course_id\n" +
                "left join teachers t on t.id = ts.teacher_id\n" +
                "where c.state = 1, u.id = ? ORDER BY c.sort ASC LIMIT ?, ?";
        String countSql = "select count(*)\n" +
                "from users u\n" +
                "left join teachers_subjects ts on u.info_id = ts.teacher_id\n" +
                "left join teachers_courses tc on ts.teacher_id = tc.teacher_id\n" +
                "left join courses c on c.id = tc.course_id\n" +
                "where c.state = 1, u.id = ?";

        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                PreparedStatement countPs = conn.prepareStatement(countSql)
        ) {
            ps.setInt(1, roleId);
            ps.setInt(2, (pageIndex - 1) * pageSize);
            ps.setInt(3, pageSize);
            ResultSet rs = ps.executeQuery();
            List<Course> courses = new ArrayList<>();
            while (rs.next()) {
                Course course = new Course();
                course.setId(rs.getInt("id"));
                course.setName(rs.getString("course_name"));
                course.setSubjectId(rs.getInt("subject_id"));
                course.setSort(rs.getInt("sort"));
                course.setCreateDate(rs.getDate("create_date"));
                course.setDescription(rs.getString("description"));
                course.setState(rs.getBoolean("state"));
                courses.add(course);
            }

            // 查询总数
            countPs.setInt(1, roleId);
            ResultSet countRs = countPs.executeQuery();
            int totalCount = 0;
            if (countRs.next()) {
                totalCount = countRs.getInt(1);
            }

            int totalPages = (int) Math.ceil((double) totalCount / pageSize);
            return new PageHelper<>(courses, pageIndex, pageSize, totalPages, "/coursemanage/", totalCount);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 根据课程名称来查询课程（分页）
     *
     * @param courseName
     * @return
     */
    public PageHelper<Course> findPageSelectByCourseName(String courseName, int pageIndex, int pageSize) throws SQLException {
        String sql = "SELECT * FROM courses WHERE course_name LIKE ? AND state = 1 ORDER BY sort ASC LIMIT ?, ?";
        String countSql = "SELECT COUNT(*) FROM courses WHERE course_name LIKE ? AND state = 1";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                PreparedStatement countPs = conn.prepareStatement(countSql)
        ) {
            // 查询数据
            ps.setString(1, "%" + courseName + "%");
            ps.setInt(2, (pageIndex - 1) * pageSize);
            ps.setInt(3, pageSize);
            ResultSet rs = ps.executeQuery();

            List<Course> courses = new ArrayList<>();
            while (rs.next()) {
                Course course = new Course();
                course.setId(rs.getInt("id"));
                course.setName(rs.getString("course_name"));
                course.setSubjectId(rs.getInt("subject_id"));
                course.setSort(rs.getInt("sort"));
                course.setCreateDate(rs.getDate("create_date"));
                course.setDescription(rs.getString("description"));
                course.setState(rs.getBoolean("state"));
                courses.add(course);
            }

            // 查询总数
            countPs.setString(1, "%" + courseName + "%");
            ResultSet countRs = countPs.executeQuery();
            int totalCount = 0;
            if (countRs.next()) {
                totalCount = countRs.getInt(1);
            }

            int totalPages = (int) Math.ceil((double) totalCount / pageSize);
            return new PageHelper<>(courses, pageIndex, pageSize, totalPages, "/coursemanage/search", totalCount);
        }
    }

    /**
     * 根据专业名称来查询课程（分页）
     *
     * @param subjectName
     * @return
     */
    public PageHelper<Course> findPageSelectBySubjectName(String subjectName, int pageIndex, int pageSize) throws SQLException {
        String sql = "SELECT c.* FROM courses c LEFT JOIN subjects s ON c.subject_id = s.id WHERE s.sub_name LIKE ? AND c.state = 1 ORDER BY c.sort ASC LIMIT ?, ?";
        String countSql = "SELECT COUNT(*) FROM courses c LEFT JOIN subjects s ON c.subject_id = s.id WHERE s.sub_name LIKE ? AND c.state = 1";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                PreparedStatement countPs = conn.prepareStatement(countSql)
        ) {
            // 查询数据
            ps.setString(1, "%" + subjectName + "%");
            ps.setInt(2, (pageIndex - 1) * pageSize);
            ps.setInt(3, pageSize);
            ResultSet rs = ps.executeQuery();

            List<Course> courses = new ArrayList<>();
            while (rs.next()) {
                Course course = new Course();
                course.setId(rs.getInt("id"));
                course.setName(rs.getString("course_name"));
                course.setSubjectId(rs.getInt("subject_id"));
                course.setSort(rs.getInt("sort"));
                course.setCreateDate(rs.getDate("create_date"));
                course.setDescription(rs.getString("description"));
                course.setState(rs.getBoolean("state"));
                courses.add(course);
            }

            // 查询总数
            countPs.setString(1, "%" + subjectName + "%");
            ResultSet countRs = countPs.executeQuery();
            int totalCount = 0;
            if (countRs.next()) {
                totalCount = countRs.getInt(1);
            }

            int totalPages = (int) Math.ceil((double) totalCount / pageSize);
            return new PageHelper<>(courses, pageIndex, pageSize, totalPages, "/coursemanage/search", totalCount);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据课程和专业名称来查询课程
     *
     * @param courseName
     * @return
     */
    public PageHelper<Course> findCoursesByCourseAndSubjectName(String courseName, String subjectName, int pageIndex, int pageSize) throws Exception {
        System.out.println("============================");
        String sql = "SELECT c.* FROM courses c LEFT JOIN subjects s ON c.subject_id = s.id WHERE c.course_name LIKE ? AND s.sub_name LIKE ? AND c.state = 1 ORDER BY c.sort ASC LIMIT ?, ?";
        String countSql = "SELECT COUNT(*) FROM courses c LEFT JOIN subjects s ON c.subject_id = s.id WHERE c.course_name LIKE ? AND s.sub_name LIKE ? AND c.state = 1";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                PreparedStatement countPs = conn.prepareStatement(countSql)
        ) {
            // 查询数据
            ps.setString(1, "%" + courseName + "%");
            ps.setString(2, "%" + subjectName + "%");
            ps.setInt(3, (pageIndex - 1) * pageSize);
            ps.setInt(4, pageSize);
            ResultSet rs = ps.executeQuery();

            List<Course> courses = new ArrayList<>();
            while (rs.next()) {
                Course course = new Course();
                course.setId(rs.getInt("id"));
                course.setName(rs.getString("course_name"));
                course.setSubjectId(rs.getInt("subject_id"));
                course.setSort(rs.getInt("sort"));
                course.setCreateDate(rs.getDate("create_date"));
                course.setDescription(rs.getString("description"));
                course.setState(rs.getBoolean("state"));
                courses.add(course);
            }

            // 查询总数
            countPs.setString(1, "%" + courseName + "%");
            countPs.setString(2, "%" + subjectName + "%");
            ResultSet countRs = countPs.executeQuery();
            int totalCount = 0;
            if (countRs.next()) {
                totalCount = countRs.getInt(1);
            }
            System.out.println(totalCount);

            int totalPages = (int) Math.ceil((double) totalCount / pageSize);
            System.out.println(totalPages);
            return new PageHelper<>(courses, pageIndex, pageSize, totalCount, "/coursemanage/search",totalPages);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据ID查到的信息来修改课程信息
     */
    public boolean updateCourses(Course course) throws Exception {
        String sql = "UPDATE courses SET course_name = ?, subject_id = ?,description = ?, state = ? WHERE id = ?";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql)
        ) {
            ps.setString(1, course.getName());
            ps.setInt(2, course.getSubjectId());
            ps.setString(3, course.getDescription());
            ps.setBoolean(4, course.isState());
            ps.setInt(5, course.getId());
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 根据专业名称查询排序
     */
    public List<Course> findSelectBySubjectName(String subjectName) throws SQLException {
        String sql = "SELECT c.* FROM courses c LEFT JOIN subjects s ON c.subject_id = s.id WHERE s.sub_name LIKE ? AND c.state = 1 ORDER BY c.sort ASC";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
        ) {
            // 查询数据
            ps.setString(1, "%" + subjectName + "%");
            ResultSet rs = ps.executeQuery();

            List<Course> courses = new ArrayList<>();
            while (rs.next()) {
                Course course = new Course();
                course.setId(rs.getInt("id"));
                course.setName(rs.getString("course_name"));
                course.setSubjectId(rs.getInt("subject_id"));
                course.setSort(rs.getInt("sort"));
                course.setCreateDate(rs.getDate("create_date"));
                course.setDescription(rs.getString("description"));
                course.setState(rs.getBoolean("state"));
                courses.add(course);
            }
            return courses;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 根据排序之后的顺序来保存课程
     */
    public boolean saveCourseBySort(List<Course> courses) {
        String sql = "UPDATE courses SET sort = ? WHERE id = ?";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
        ) {
            conn.setAutoCommit(false); // 开启事务

            for (int i = 0; i < courses.size(); i++) {
                Course course = courses.get(i);
                ps.setInt(1, i + 1); // 排序从1开始
                ps.setInt(2, course.getId());
                ps.addBatch(); // 添加到批处理
            }

            ps.executeBatch(); // 执行批处理
            conn.commit(); // 提交事务
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                // 如果出错，回滚事务
                DBUtil.getConnection().rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        }
    }

    /**
     * 根据课程来查找老师
     */
    public List<Teacher> findCourseByTeacher(int courseId) {  // 修改返回类型
        String sql = """
                SELECT t.teacher_name,t.id
                FROM teachers_courses  ts
                LEFT JOIN teachers t ON ts.teacher_id = t.id
                WHERE course_id = ?
                """;
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
        ) {

            ps.setInt(1, courseId);
            ResultSet rs = ps.executeQuery();
            List<Teacher> teacherCourses = new ArrayList<>();
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setName(rs.getString("teacher_name"));
                teacher.setId(rs.getInt("id"));
                teacherCourses.add(teacher);
            }
            return teacherCourses;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }



    /**
     * 获取所有老师
     */
    public List<Teacher> findAllTeachers() {
        String sql = "SELECT * FROM teachers";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
        ) {
            ResultSet rs = ps.executeQuery();
            List<Teacher> teachers = new ArrayList<>();
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("teacher_name"));
                teacher.setPhone(rs.getString("phone"));
                teacher.setState(rs.getBoolean("state"));
                teachers.add(teacher);
            }
            return teachers;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 添加课程老师
     */
    public boolean setCourseTeacher(int courseId, int teacherId) {
        String sql = "INSERT INTO teachers_courses (course_id, teacher_id) VALUES (?, ?)";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
        ) {
            ps.setInt(1, courseId);
            ps.setInt(2, teacherId);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean removeTeacherFromCourse(int teacherId, int courseId){
        String sql = "DELETE FROM teachers_courses WHERE teacher_id = ? AND course_id = ?";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
        ) {
            ps.setInt(1, teacherId);
            ps.setInt(2, courseId);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public List<Course> findBySubjectId(int subjectId) throws Exception {
        String sql = "SELECT * FROM courses WHERE subject_id = ? AND state = 1";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                ){
            ps.setInt(1, subjectId);
            ResultSet rs = ps.executeQuery();
            List<Course> courses = new ArrayList<>();
            while (rs.next()) {
                Course course = new Course();
                course.setId(rs.getInt("id"));
                course.setName(rs.getString("course_name"));
                course.setSubjectId(rs.getInt("subject_id"));
                course.setSort(rs.getInt("sort"));
                course.setCreateDate(rs.getDate("create_date"));
                course.setDescription(rs.getString("description"));
                course.setState(rs.getBoolean("state"));
                courses.add(course);
            }
            return courses;
        }
    }

    public int findCourseIdByStudentId(int studentId) {
        String sql = "SELECT cs.course_id FROM classes cs  LEFT JOIN students s ON s.class_id = cs.id WHERE s.id = ?";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
        ) {
            ps.setInt(1, studentId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt("course_id");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public List<Course> findAllCourseIdAndName() throws Exception {
        String sql = "SELECT id, course_name FROM courses where state !=0";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
        ) {
            ResultSet rs = ps.executeQuery();
            List<Course> courses = new ArrayList<>();
            while (rs.next()) {
                Course course = new Course();
                course.setId(rs.getInt("id"));
                course.setName(rs.getString("course_name"));
                courses.add(course);
            }
            return courses;
        }
    }

}
